﻿/// <remarks>
/// 系统名称            :   ITALKI 支付系统
/// 模块名称            :   Payment
/// 文件代码            :   
/// 功能概述            :   
/// 创建人              :   Snail
/// 创建日期            :   2008.08.28
/// 版本号              :   1.0.0.0
/// ----------< 修改履历 >---------------------------------------------------------------
/// 修改日期          修改人              修改内容                           版本号
/// 2008.08.28      snail             创建文件。                         1.0.0.0
/// ------------------------------------------------------------------------------------
/// (C) 2007-2008 上海微语信息技术有限公司 版权所有，保留所有权利。
/// ------------------------------------------------------------------------------------
///</remarks>


using IProvider.Config;
using IProvider.Interface;
using Model.WWW_User.User;
using Model.WWW_WWW.Payment;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System;
using System.Data;

namespace Provider.SqlClient
{
    /// <summary>
    /// Payment数据提供者。
    /// </summary>
    public class PaymentProvider : IPaymentProvider
    {
        /// <summary>
        /// 创建学生帐户。
        /// </summary>
        /// <param name="acc">帐户对象。</param>
        /// <returns>受影响的行数。</returns>
        public override int CreateSAccount(Model.WWW_WWW.Payment.Account acc) {
            using (MySqlConnection conn = new MySqlConnection(WWW_WWW_Payment_Config.DbConnStr)) {

                const string sql = @"CreateSAccount";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddRange(
                    new MySqlParameter[]{
                        new MySqlParameter("@memberno",acc.MemberNo)
                        ,new MySqlParameter("@availableamount",acc.AvailableAmount)
                        ,new MySqlParameter("@frozenamount",acc.FrozenAmount)
                        ,new MySqlParameter("@createdate",acc.CreateDate)
                        ,new MySqlParameter("@password",acc.Password)
                        ,new MySqlParameter("@state",acc.State)
                        }
                        );
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }


        /// <summary>
        /// 创建教师帐户。
        /// </summary>
        /// <param name="acc">帐户对象。</param>
        /// <returns>受影响的行数。</returns>
        public override int CreateTAccount(Model.WWW_WWW.Payment.Account acc) {
            using (MySqlConnection conn = new MySqlConnection(WWW_WWW_Payment_Config.DbConnStr)) {

                const string sql = @"CreateTAccount";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddRange(
                    new MySqlParameter[]{
                        new MySqlParameter("@memberno",acc.MemberNo)
                        ,new MySqlParameter("@availableamount",acc.AvailableAmount)
                        ,new MySqlParameter("@frozenamount",acc.FrozenAmount)
                        ,new MySqlParameter("@createdate",acc.CreateDate)
                        ,new MySqlParameter("@password",acc.Password)
                        ,new MySqlParameter("@state",acc.State)
                        }
                        );
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }


        /// <summary>
        /// 是否已经创建了帐户。
        /// </summary>
        /// <param name="memberno">会员编号。</param>
        /// <returns>是否已经创建了帐户。</returns>
        public override bool GetHasAccount(string memberno) {
            using (MySqlConnection conn = new MySqlConnection(WWW_WWW_Payment_Config.DbConnStr)) {

                const string sql = @"select count(0) from t_paymentaccount where memberno = @memberno";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.Add(new MySqlParameter("@memberno", memberno));
                conn.Open();
                return int.Parse(cmd.ExecuteNonQuery().ToString()) > 0 ? true : false;
            }
        }


        /// <summary>
        /// 获得用户信息。
        /// </summary>
        /// <param name="memberno">会员编号。</param>
        /// <returns>UserProfile</returns>
        public override UserProfile GetUserModel(string memberno) {
            using (SqlConnection conn = new SqlConnection(WWW_User_UserConfig.DbConnStr)) {

                const string sql = @"select c.*,d.address,d.memberno teacherno from (select a.memberno, a.memberemail,b.firstname,b.lastname,b.gender,b.bod,b.country,b.city,b.zipcode from t_user a inner join t_user_detail b on a.memberno = b.memberno where a.memberno = @memberno) c left join t_teacherinfo d on c.memberno = d.memberno";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@memberno", memberno));
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                UserProfile p = null;
                if (dr.Read()) {
                    p = new UserProfile();
                    p.MemberEmail = (dr["memberemail"] == DBNull.Value ? "" : dr["memberemail"].ToString());
                    p.FirstName = (dr["firstname"] == DBNull.Value ? "" : dr["firstname"].ToString());
                    p.LastName = (dr["lastname"] == DBNull.Value ? "" : dr["lastname"].ToString());
                    p.Gender = (dr["gender"] == DBNull.Value ? "" : dr["gender"].ToString());
                    p.BOD = (dr["bod"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dr["bod"].ToString()));
                    p.Country = (dr["country"] == DBNull.Value ? "" : dr["country"].ToString());
                    p.City = (dr["city"] == DBNull.Value ? "" : dr["city"].ToString());
                    p.Address = (dr["address"] == DBNull.Value ? "" : dr["address"].ToString());
                    p.ZipCode = (dr["zipcode"] == DBNull.Value ? "" : dr["zipcode"].ToString());
                    p.IsTeacher = (dr["zipcode"] == DBNull.Value ? false : true);
                }
                return p;
            }
        }

        /// <summary>
        /// 课程支付。
        /// </summary>
        /// <param name="SessionID">课程订单编号。</param>
        /// <param name="memberNO">学生会员编号。</param>
        /// <param name="payPassword">支付密码。</param>
        /// <returns>True成功支付。False支付失败。</returns>
        public override bool Pay(long sessionID, string memberNO, string payPassword) {
            Session sessionObj = GetSession(sessionID);
            //检查订单的所有者是否和memberNo匹配。
            if (sessionObj.SNO != memberNO) {
                //TODO 测试代码
                throw new InvalidOperationException("身份校验失败!");
            }

            //检查订单的状态
            if (sessionObj.State != SessionStatus.TeacherAccept) {
                //TODO 测试代码
                throw new InvalidOperationException("Session状态无效!");
            }

            using (MySqlConnection conn = new MySqlConnection(WWW_WWW_Payment_Config.DbConnStr)) {
                const string sql = @"Pay";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddRange(
                    new MySqlParameter[]{
                        new MySqlParameter("@sessionid",MySqlDbType.Int64)
                        ,new MySqlParameter("@amount",MySqlDbType.Decimal)
                        ,new MySqlParameter("@memberno",memberNO)
                        ,new MySqlParameter("@memberno2",sessionObj.TNO)
                        ,new MySqlParameter("@paypassword",payPassword)
                        }
                        );
                cmd.Parameters[0].Value = sessionID;
                conn.Open();
                return true;

            }
        }


        /// <summary>
        /// 获得课程订单。
        /// </summary>
        /// <param name="sessionID">课程订单编号。</param>
        /// <returns>Session对象。</returns>
        public override Session GetSession(long sessionID) {
            using (SqlConnection conn = new SqlConnection(WWW_User_UserConfig.DbConnStr)) {
                const string sql = @"select sessionid,sessiontype,cid,lid,ctitle,ltitle,sno,tno,languagelearn,baselanguage,sessioncount,startdate,starttime,longtime,price,state,smarkstate,smark,tmarkstate,tmark,teachtool,professional,knowledgeable,helpful,stotdesc,ttosdesc,lchapter from t_sessions where sessionid = @sessionid";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@sessionid", SqlDbType.BigInt));
                cmd.Parameters[0].Value = sessionID;
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                Session model = null;
                if (dr.Read()) {
                    model = new Session();
                    if (dr["SessionID"].ToString() != "") {
                        model.SessionID = int.Parse(dr["SessionID"].ToString());
                    }
                    model.SessionType = dr["SessionType"].ToString();
                    if (dr["CID"].ToString() != "") {
                        model.CID = int.Parse(dr["CID"].ToString());
                    }
                    if (dr["LId"].ToString() != "") {
                        model.LId = int.Parse(dr["LId"].ToString());
                    }
                    model.CTitle = dr["CTitle"].ToString();
                    model.LTitle = dr["LTitle"].ToString();
                    model.SNO = dr["SNO"].ToString();
                    model.TNO = dr["TNO"].ToString();
                    model.LanguageLearn = dr["LanguageLearn"].ToString();
                    model.BaseLanguage = dr["BaseLanguage"].ToString();
                    if (dr["SessionCount"].ToString() != "") {
                        model.SessionCount = int.Parse(dr["SessionCount"].ToString());
                    }
                    model.StartDate = dr["StartDate"].ToString();
                    if (dr["StartTime"].ToString() != "") {
                        model.StartTime = int.Parse(dr["StartTime"].ToString());
                    }
                    if (dr["LongTime"].ToString() != "") {
                        model.LongTime = int.Parse(dr["LongTime"].ToString());
                    }
                    if (dr["Price"].ToString() != "") {
                        model.Price = decimal.Parse(dr["Price"].ToString());
                    }
                    char state = dr["State"].ToString().ToUpper()[0];
                    model.State = (state == '0' ? SessionStatus.Request :
                    state == '1' ? SessionStatus.StudentCancel :
                    state == '2' ? SessionStatus.Timeout :
                    state == '3' ? SessionStatus.ModificationPrice :
                    state == '4' ? SessionStatus.TeacherDecline :
                    state == '5' ? SessionStatus.TeacherAccept :
                    state == '6' ? SessionStatus.Paid :
                    state == '7' ? SessionStatus.ClassOver :
                    state == '8' ? SessionStatus.TeacherCancel :
                    state == '9' ? SessionStatus.Refund_Consistent :
                    state == 'A' ? SessionStatus.Refund_NoCredit :
                    state == 'B' ? SessionStatus.TeacherConfirm :
                    state == 'C' ? SessionStatus.StudentComplaint :
                    state == 'D' ? SessionStatus.Refund_NoAttestation :
                    state == 'E' ? SessionStatus.PaidToTeacher_Attestation :
                    state == 'F' ? SessionStatus.PaidToTeacher_StudentConfirm :
                    state == 'G' ? SessionStatus.TeacherAppraised :
                    SessionStatus.Unknown
                    );
                    model.SMarkState = dr["SMarkState"].ToString();
                    model.SMark = dr["SMark"].ToString();
                    model.TMarkState = dr["TMarkState"].ToString();
                    model.TMark = dr["TMark"].ToString();
                    model.TeachTool = dr["TeachTool"].ToString();
                    if (dr["Professional"].ToString() != "") {
                        model.Professional = int.Parse(dr["Professional"].ToString());
                    }
                    if (dr["Knowledgeable"].ToString() != "") {
                        model.Knowledgeable = int.Parse(dr["Knowledgeable"].ToString());
                    }
                    if (dr["Helpful"].ToString() != "") {
                        model.Helpful = int.Parse(dr["Helpful"].ToString());
                    }
                    model.SToTDesc = dr["SToTDesc"].ToString();
                    model.TToSDesc = dr["TToSDesc"].ToString();
                    if (dr["LChapter"].ToString() != "") {
                        model.LChapter = int.Parse(dr["LChapter"].ToString());
                    }
                }
                return model;
            }
        }
    }
}